from django.db import models

# Create your models here.
import pymysql


class BOOK:
    def __init__(self):
        self.con = pymysql.connect(host='localhost', port=3306, user='root',
                                   password='8852348', database='s28',
                                   charset='utf8')
        self.sor = self.con.cursor()

    def __del__(self):
        self.con.close()
        self.sor.close()

    def search_id(self, phone_or_user,arr):
        """查询用户名"""
        sql = 'select id from user_information where {}=%s'.format(phone_or_user)
        self.sor.execute(sql, (arr,))
        ret = self.sor.fetchall()
        #print(ret, type(ret))
        if len(ret) == 0:
            return True
        else:
            return ret[0][0]
    def search_to_id(self, phone_or_user,arr):
        """查询用户名"""
        sql = 'select user from user_information where {}=%s'.format(phone_or_user)
        self.sor.execute(sql, (arr,))
        ret = self.sor.fetchall()
        #print(ret, type(ret))
        if len(ret) == 0:
            return True
        else:
            return ret[0][0]

    def search_user(self, arr):
        """查询用户名"""
        sql = 'select user from user_information where user=%s'
        self.sor.execute(sql, (arr,))
        ret = self.sor.fetchall()

        if len(ret) == 0:
            return True
        else:
            return ret[0][0]

    def search_password(self, arr):
        """查询密码"""
        sql = 'select password from user_information where password=%s'
        self.sor.execute(sql, (arr,))
        ret = self.sor.fetchall()
        print(ret)

    def search_mobile_phone(self, arr):
        """查询手机号是否存在"""
        sql = 'select mobile_phone from user_information where  mobile_phone=%s'
        self.sor.execute(sql, (arr,))
        ret = self.sor.fetchall()
        print(ret, type(ret))
        if len(ret) == 0:
            return 1
        else:
            # print('查到的数据', ret[0])
            return ret[0][0]
    def user_info(self, user, password):
        """查询账号密码是否是正确的"""
        sql = 'select * from user_information where user="{}" and password=%s'.format(user)
        print(sql)
        self.sor.execute(sql, (password,))
        ret = self.sor.fetchall()
        print(ret)
        if ret == ():
            return '没有信息'
        else:
            return '欢迎{0}'.format(ret[0][1])


    def search_info(self, args, arr):
        """args查询的值  arr 条件"""
        # ex = {'user': 'user', 'pwd': 'password', 'phone': 'mobile_phone'}
        ex = ('user', 'password', 'mobile_phone')
        # sql = 'select mobile_phone from user_information where  mobile_phone=%s'
        sql = 'select {0} from user_information where  mobile_phone=%s'.format(args)
        self.sor.execute(sql, (arr,))
        ret = self.sor.fetchall()
        print(ret[0][0])

    def insert_info(self, user, password, phone):

        sql = 'insert into user_information (user,password,mobile_phone) values (%s,%s,%s)'
        self.sor.execute(sql, (user, password, phone))
        self.con.commit()


    def re_user_info(self, phone_or_user, user, password):
        """查询账号密码是否是正确的"""
        sql = 'select * from user_information where {}="{}" and password=%s'.format(phone_or_user, user)
        self.sor.execute(sql, (password,))
        ret = self.sor.fetchall()
        if ret == ():
            return '没有信息'
        else:
            return ret[0][1], ret[0][2]
